With Check Option

WITH CHECK OPTION#

Views usually contain a subset of rows from a base table. It is possible to insert or update rows which are not visible through the view. The WITH CHECK OPTION clause is used at the time of creation of the view and is used to maintain consistency when updating a table through an updatable view. This clause forbids the user to insert or update rows that are not visible through the view.

Syntax#

CREATE [OR REPLACE] VIEW view_name AS

select_statement

WITH CHECK OPTION;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/43lesson.sh and wait for the MySQL prompt to start-up.

1. Suppose we want a view to show details of only those actors who are single, we can create it using the following statement:

   CREATE VIEW SingleActors AS 
   SELECT FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions 
   FROM Actors 
   WHERE MaritalStatus = 'Single';
   

   This view contains four rows as shown:

2. Since the SingleActors view is updatable, we can insert a row in the Actors table through it. To show the inconsistency that can arise, we will insert a married actor in the table. Execute the following query to insert a new actor:

   INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus,  NetWorthInMillions) 
   VALUES ('Tom', 'Hanks', '1956-07-09', 'Male', 'Married', 350);
   

   The insert operation is successful as the row appears in the table.

But the newly inserted row doesn’t appear in the SingleActors view.

This is because the row we just added does not qualify to appear in the view. The purpose of this view was to display single actors only. However, the view can still be used to make unwanted changes to the Actors table.

3. The WITH CHECK OPTION clause restricts users to update or insert data which is visible through the view. We will update the SingleActors view created in step 1 using the following query:

   CREATE OR REPLACE VIEW SingleActors AS 
   SELECT FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions 
   FROM Actors 
   WHERE MaritalStatus = 'Single' 
   WITH CHECK OPTION;
   

   If we omit the OR REPLACE clause from the above query, we will get an error because a view with this name already exists.

   Now try to insert a row in the Actors table through the SingleActors view as follows:

   INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions) 
   VALUES ('Matt', 'Damon', '1970-10-08', 'Male', 'Married', 160);
   

We encounter the CHECK OPTION failed error message. The WITH CHECK OPTION clause ensures that only actors who are single can be inserted into the Actors table through this view. Now execute the following query:

INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions) 
VALUES ('Charlize', 'Theron', '1975-08-07', 'Female', 'Single', 130);

The row is inserted in the table and is visible through the view as well.